﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;

using HsTec.Mobile.Green.Util;

namespace HsTec.Mobile.Green.DAL
{
    /// <summary>
    /// 数据访问类FixedRatio。
    /// </summary>
    public class FixedRatioDAL
    {
        public FixedRatioDAL()
        { }
        #region  成员方法
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string sExistsWhere)
        {
            return SQLHelper.Exists("tb_FixedRatio", sExistsWhere);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(HsTec.Mobile.Green.Model.FixedRatio model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tb_FixedRatio(");
            strSql.Append("Years,MonthRatio)");
            strSql.Append(" values (");
            strSql.Append("@Years,@MonthRatio)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@Years", SqlDbType.Char,4),
					new SqlParameter("@MonthRatio", SqlDbType.Decimal,5)};
            parameters[0].Value = model.Years;
            parameters[1].Value = model.MonthRatio;

            return SQLHelper.ExecuteNonQuery(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update(HsTec.Mobile.Green.Model.FixedRatio model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tb_FixedRatio set ");
            strSql.Append("Years=@Years,");
            strSql.Append("MonthRatio=@MonthRatio");
            strSql.Append(" where FR_ID=@FR_ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@FR_ID", SqlDbType.Int,4),
					new SqlParameter("@Years", SqlDbType.Char,4),
					new SqlParameter("@MonthRatio", SqlDbType.Decimal,5)};
            parameters[0].Value = model.FR_ID;
            parameters[1].Value = model.Years;
            parameters[2].Value = model.MonthRatio;

            return SQLHelper.ExecuteNonQuery(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int Delete(string sIDs)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tb_FixedRatio ");
            strSql.Append(" where FR_ID in ( " + sIDs + " )");
            return SQLHelper.ExecuteNonQuery(strSql.ToString());
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public HsTec.Mobile.Green.Model.FixedRatio GetModel(int FR_ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 FR_ID,Years,MonthRatio from tb_FixedRatio ");
            strSql.Append(" where FR_ID=@FR_ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@FR_ID", SqlDbType.Int,4)};
            parameters[0].Value = FR_ID;

            HsTec.Mobile.Green.Model.FixedRatio model = new HsTec.Mobile.Green.Model.FixedRatio();
            DataSet ds = SQLHelper.ExecuteDataset(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["FR_ID"].ToString() != "")
                {
                    model.FR_ID = int.Parse(ds.Tables[0].Rows[0]["FR_ID"].ToString());
                }
                model.Years = ds.Tables[0].Rows[0]["Years"].ToString();
                if (ds.Tables[0].Rows[0]["MonthRatio"].ToString() != "")
                {
                    model.MonthRatio = decimal.Parse(ds.Tables[0].Rows[0]["MonthRatio"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataView GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FR_ID,Years,MonthRatio ");
            strSql.Append(" FROM tb_FixedRatio ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return SQLHelper.ExecuteDataView(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataView GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" FR_ID,Years,MonthRatio ");
            strSql.Append(" FROM tb_FixedRatio ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return SQLHelper.ExecuteDataView(strSql.ToString());
        }
        #endregion  成员方法

        /// <summary>
        /// 获取修正比例 
        /// </summary>
        /// <param name="years"></param>
        /// <param name="iCaliber">口径 1:口径1   2:口径2</param>
        /// <returns></returns>
        public DataView GetFixedRatioList(string sYears, int iCaliber)
        {
            DateTime dLastYears = DateTime.Parse(sYears + "-01-01").AddYears(-1);
            string sLastYears = dLastYears.ToString("yyyy");
            string sLastYearMonth = dLastYears.ToString("yyyyMM");
            DataTable dt = new DataTable("dt_FixedRatio");
            dt.Columns.Add(new DataColumn("ID", typeof(int)));
            dt.Columns.Add(new DataColumn("Years", typeof(string)));
            dt.Columns.Add(new DataColumn("TotalTraffic", typeof(string)));
            dt.Columns.Add(new DataColumn("TotalPower", typeof(string)));
            dt.Columns.Add(new DataColumn("TotalTrafficPower", typeof(string)));
            dt.Columns.Add(new DataColumn("TrafficPower", typeof(string)));
            dt.Columns.Add(new DataColumn("CalcMonth", typeof(string)));
            dt.Columns.Add(new DataColumn("MonthRatio", typeof(string)));
            string sql = String.Empty;
            string sTraffic = String.Empty;
            string sPowers = String.Empty;
            Double dFixedRatio = 0.00;
            if (iCaliber == 1)
            {
                //1.计算全年的单位业务能耗
                //1.1 获取总业务量=数据量+话务量=(2G+3G)+(话务量+TD话务量)
                string s2gFlowd = SQLHelper.ExecuteString("select sum(Value) from rt_2GFlow where substring(Years,1,4) = '" + sLastYears + "' and Area_ID='41'");
                string s3gFlowd = SQLHelper.ExecuteString("select sum(Value) from rt_3GFlow where substring(Years,1,4) = '" + sLastYears + "' and Area_ID='41'");
                string sPhoned = SQLHelper.ExecuteString("select sum(Value*5.85) from rt_Telephone where substring(Years,1,4) = '" + sLastYears + "' and Area_ID='41'");
                string sTDd = SQLHelper.ExecuteString("select sum(Value*5.49) from rt_TDTraffic where substring(Years,1,4) = '" + sLastYears + "' and Area_ID='41'");
                double dTraffic = (Double.Parse(String.IsNullOrEmpty(s2gFlowd) ? "0" : s2gFlowd) +
                                Double.Parse(String.IsNullOrEmpty(s3gFlowd) ? "0" : s3gFlowd) +
                                Double.Parse(String.IsNullOrEmpty(sPhoned) ? "0" : sPhoned) +
                                Double.Parse(String.IsNullOrEmpty(sTDd) ? "0" : sTDd));

                string sTotalPowers = SQLHelper.ExecuteString("select sum(Power) from rt_EnergyConsumption where substring(Years,1,4)='" + sLastYears + "' and Area_ID='41'");
                if (String.IsNullOrEmpty(sTotalPowers))
                {
                    return null;
                }
                Double dTotalFixedRatio = 0.00;
                try
                {
                    dTotalFixedRatio = Double.Parse(sTotalPowers) / dTraffic;
                }
                catch
                {

                }
                //1.分月计算每个月份的耗电量和业务量
                for (int i = 1; i <= 12; i++)
                {
                    DataRow dr = dt.NewRow();
                    dr["ID"] = i.ToString();
                    dr["Years"] = sYears + i.ToString().PadLeft(2, '0');
                    s2gFlowd = SQLHelper.ExecuteString("select sum(Value) from rt_2GFlow where Years>='" + sLastYears + "01' and  Years <= '" + sLastYears + i.ToString().PadLeft(2, '0') + "' and Area_ID='41'");
                    s3gFlowd = SQLHelper.ExecuteString("select sum(Value) from rt_3GFlow where Years>='" + sLastYears + "01' and Years <= '" + sLastYears + i.ToString().PadLeft(2, '0') + "' and Area_ID='41'");
                    sPhoned = SQLHelper.ExecuteString("select sum(Value*5.85) from rt_Telephone where Years>='" + sLastYears + "01' and Years <= '" + sLastYears + i.ToString().PadLeft(2, '0') + "' and Area_ID='41'");
                    sTDd = SQLHelper.ExecuteString("select sum(Value*5.49) from rt_TDTraffic where Years>='" + sLastYears + "01' and Years <= '" + sLastYears + i.ToString().PadLeft(2, '0') + "' and Area_ID='41'");
                    dTraffic = (Double.Parse(String.IsNullOrEmpty(s2gFlowd) ? "0" : s2gFlowd) +
                                    Double.Parse(String.IsNullOrEmpty(s3gFlowd) ? "0" : s3gFlowd) +
                                    Double.Parse(String.IsNullOrEmpty(sPhoned) ? "0" : sPhoned) +
                                    Double.Parse(String.IsNullOrEmpty(sTDd) ? "0" : sTDd));

                    sPowers = SQLHelper.ExecuteString("select sum(Power) from rt_EnergyConsumption where Years>='" + sLastYears + "01' and Years <= '" + sLastYears + i.ToString().PadLeft(2, '0') + "' and Area_ID='41'");
                    if (String.IsNullOrEmpty(sPowers))
                    {
                        return null;
                    }
                    if (dTraffic == 0)
                    {
                        return null;
                    }
                    dFixedRatio = Double.Parse(sPowers) / dTraffic;
                    dr["TotalTraffic"] = dTraffic.ToString("0.00");
                    dr["TotalPower"] = Double.Parse(sPowers).ToString("0.00");
                    dr["TrafficPower"] = dFixedRatio.ToString("0.00");
                    dr["TotalTrafficPower"] = dTotalFixedRatio.ToString("0.00");
                    try
                    {
                        dr["CalcMonth"] = (dFixedRatio / dTotalFixedRatio).ToString("0.00");
                    }
                    catch
                    {
                        dr["CalcMonth"] = "0.00";
                    }
                    string tmpFR = SQLHelper.ExecuteString("select MonthRatio from tb_FixedRatio where Years='" + sYears + i.ToString().PadLeft(2, '0') + "'");
                    if (String.IsNullOrEmpty(tmpFR))
                    {
                        dr["MonthRatio"] = dr["CalcMonth"];
                    }
                    else
                    {
                        dr["MonthRatio"] = SQLHelper.ExecuteString("select MonthRatio from tb_FixedRatio where Years='" + sYears + i.ToString().PadLeft(2, '0') + "'");
                    }
                    dt.Rows.Add(dr);
                }
            }
            else
            {
                //1.计算全年的单位业务能耗
                //1.1 获取总业务量=数据量+话务量=(2G+3G)+(话务量+TD话务量)
                string sPDCH = SQLHelper.ExecuteString("select sum(Value) from rt_PDCH where substring(Years,1,4) = '" + sLastYears + "' and Area_ID='41'");
                string sPhoned = SQLHelper.ExecuteString("select sum(Value) from rt_Telephone where substring(Years,1,4) = '" + sLastYears + "' and Area_ID='41'");
                string sTDd = SQLHelper.ExecuteString("select sum(Value) from rt_TDTraffic where substring(Years,1,4) = '" + sLastYears + "' and Area_ID='41'");
                double dTraffic = (Double.Parse(String.IsNullOrEmpty(sPDCH) ? "0" : sPDCH) +
                                Double.Parse(String.IsNullOrEmpty(sPhoned) ? "0" : sPhoned) +
                                Double.Parse(String.IsNullOrEmpty(sTDd) ? "0" : sTDd));

                string sTotalPowers = SQLHelper.ExecuteString("select sum(Power) from rt_EnergyConsumption where substring(Years,1,4)='" + sLastYears + "' and Area_ID='41'");
                if (String.IsNullOrEmpty(sTotalPowers))
                {
                    return null;
                }
                Double dTotalFixedRatio = 0.00;
                try
                {
                    dTotalFixedRatio = Double.Parse(sTotalPowers) / dTraffic;
                }
                catch
                {

                }
                //1.分月计算每个月份的耗电量和业务量
                for (int i = 1; i <= 12; i++)
                {
                    DataRow dr = dt.NewRow();
                    dr["ID"] = i.ToString();
                    dr["Years"] = sYears + i.ToString().PadLeft(2, '0');
                    sPDCH = SQLHelper.ExecuteString("select sum(Value) from rt_PDCH where Years>='" + sLastYears + "01' and  Years <= '" + sLastYears + i.ToString().PadLeft(2, '0') + "' and Area_ID='41'");
                    sPhoned = SQLHelper.ExecuteString("select sum(Value) from rt_Telephone where Years>='" + sLastYears + "01' and Years <= '" + sLastYears + i.ToString().PadLeft(2, '0') + "' and Area_ID='41'");
                    sTDd = SQLHelper.ExecuteString("select sum(Value) from rt_TDTraffic where Years>='" + sLastYears + "01' and Years <= '" + sLastYears + i.ToString().PadLeft(2, '0') + "' and Area_ID='41'");
                    dTraffic = (Double.Parse(String.IsNullOrEmpty(sPDCH) ? "0" : sPDCH) +
                                    Double.Parse(String.IsNullOrEmpty(sPhoned) ? "0" : sPhoned) +
                                    Double.Parse(String.IsNullOrEmpty(sTDd) ? "0" : sTDd));

                    sPowers = SQLHelper.ExecuteString("select sum(Power) from rt_EnergyConsumption where Years>='" + sLastYears + "01' and Years <= '" + sLastYears + i.ToString().PadLeft(2, '0') + "' and Area_ID='41'");
                    if (String.IsNullOrEmpty(sPowers))
                    {
                        return null;
                    }
                    if (dTraffic == 0)
                    {
                        return null;
                    }
                    dFixedRatio = Double.Parse(sPowers) / dTraffic;
                    dr["TotalTraffic"] = dTraffic.ToString("0.00");
                    dr["TotalPower"] = Double.Parse(sPowers).ToString("0.00");
                    dr["TrafficPower"] = dFixedRatio.ToString("0.00");
                    dr["TotalTrafficPower"] = dTotalFixedRatio.ToString("0.00");
                    try
                    {
                        dr["CalcMonth"] = (dFixedRatio / dTotalFixedRatio).ToString("0.00");
                    }
                    catch
                    {
                        dr["CalcMonth"] = "0.00";
                    }
                    string tmpFR = SQLHelper.ExecuteString("select MonthRatio from tb_FixedRatio where Years='" + sYears + i.ToString().PadLeft(2, '0') + "'");
                    if (String.IsNullOrEmpty(tmpFR))
                    {
                        dr["MonthRatio"] = dr["CalcMonth"];
                    }
                    else
                    {
                        dr["MonthRatio"] = tmpFR;
                    }
                    dt.Rows.Add(dr);
                }
            }
            return dt.DefaultView;
        }
    }
}

